package com.agmte.api.common.crud.core;

import com.agmte.api.app.entity.ApiInfo;
import com.agmte.api.common.crud.core.pojo.Page;
import com.agmte.api.common.crud.tableAutoCreate.FieldInfo;
import com.agmte.api.common.crud.tableAutoCreate.TableHelper;
import com.agmte.api.common.crud.tableAutoCreate.TableInfo;
import com.agmte.api.common.crud.utils.MapperXmlParser;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

@Component
public class CrudDb {

    private static JdbcTemplate jdbcTemplate;

    public static <T, M> int delete(Class<T> clazz, List<M> ids) {
        if (ids == null || ids.isEmpty()) {
            return 0; // 无需删除
        }

        // 表名和主键列名
        TableInfo table = TableHelper.getTable(clazz);
        FieldInfo pk = table.getKey();

        // 构造占位符字符串 (?, ?, ...)
        String placeholders = ids.stream()
                .map(id -> "?")
                .collect(Collectors.joining(", "));

        // 构建SQL
        String sql = "DELETE FROM " + table.getName() + " WHERE " + pk.getFieldName() + " IN (" + placeholders + ")";

        // 执行删除，ids转成Object[]
        return jdbcTemplate.update(sql, ids.toArray());
    }

    public static <T> T  byId(Class<T> clazz, Serializable id) {
        if (id == null) {
            return null;
        }
        // 表名和主键列名
        TableInfo table = TableHelper.getTable(clazz);
        FieldInfo pk = table.getKey();
        String sql = "select * from " + table.getName() + " where " + pk.getFieldName() + " = ?";

        List<T> query = jdbcTemplate.query(sql, new CustomBeanPropertyRowMapper<>(clazz), id);
        if (query.isEmpty()) {
            return null;
        }
        return query.get(0);
    }


    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        CrudDb.jdbcTemplate = jdbcTemplate;
    }

    public static <T> int saveOrUpdate(T entity) {
        TableInfo table = TableHelper.getTable(entity.getClass());
        FieldInfo pk = table.getKey();

        if (pk == null) {
            throw new RuntimeException(table.getName() + "表格为设置主键@FieldId");
        }

        try {
            Field pkField = pk.getField();
            pkField.setAccessible(true);
            Object pkValue = pkField.get(entity);

            // 更新
            List<String> columns = new ArrayList<>();
            List<Object> values = new ArrayList<>();
            for (FieldInfo fieldInfo : table.getFieldInfos()) {
                if (fieldInfo.isKey()) {
                    continue;
                }
                Field field = fieldInfo.getField();
                field.setAccessible(true);
                Object value = field.get(entity);
                if (value != null) {
                    values.add(TypeConversion.convertToDatabase(entity.getClass(), fieldInfo.getFieldName(), value));
                    columns.add(fieldInfo.getFieldName());
                }
            }
            if (pkValue != null) {
                // 检查数据库是否已有此主键
                String checkSql = "SELECT COUNT(1) FROM " + table.getName() + " WHERE " + pk.getFieldName() + " = ?";
                Integer count = jdbcTemplate.queryForObject(checkSql, Integer.class, pkValue);
                if (count != null && count > 0) {

                    String setSql = columns.stream().map(f -> f + " = ?").collect(Collectors.joining(", "));
                    String updateSql = "UPDATE " + table.getName() + " SET " + setSql + " WHERE " + pk.getFieldName() + " = ?";

                    values.add(pkValue);
                    return jdbcTemplate.update(updateSql, values.toArray());
                }
            }
            // 插入
            String insertSql = "INSERT INTO " + table.getName() +
                    " (" + String.join(",", columns) + ")" +
                    " VALUES (" + columns.stream().map(c -> "?").collect(Collectors.joining(",")) + ")";

            KeyHolder keyHolder = new GeneratedKeyHolder();

            jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
                for (int i = 0; i < values.size(); i++) {
                    ps.setObject(i + 1, values.get(i));
                }
                return ps;
            }, keyHolder);
            Object key = null;
            for (Map<String, Object> map : keyHolder.getKeyList()) {
                key = map.get(pk.getFieldName());
                pkField.set(entity, key);
            }
            return Objects.isNull(key) ? 0 : 1;

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    public static <T> List<T> list(Class<T> clazz) {
        TableInfo table = TableHelper.getTable(clazz);
        String sql = "SELECT * FROM " + table.getName();
        return jdbcTemplate.query(sql, new CustomBeanPropertyRowMapper<>(clazz));
    }

    public static <T> List<T> list(T entity) {
        TableInfo table = TableHelper.getTable(entity.getClass());
        List<String> columns = new ArrayList<>();
        List<Object> values = new ArrayList<>();
        try {
            for (FieldInfo fieldInfo : table.getFieldInfos()) {
                Field field = fieldInfo.getField();
                field.setAccessible(true);
                Object value = field.get(entity);
                if (value != null) {
                    values.add(value);
                    columns.add(fieldInfo.getFieldName());
                }
            }

            String sql = "SELECT * FROM " + table.getName();

            if (!columns.isEmpty()) {
                // 构造 WHERE 条件，假设字段名即列名，且用 = 连接
                String whereClause = columns.stream()
                        .map(col -> col + " = ?")
                        .collect(Collectors.joining(" AND "));
                sql += " WHERE " + whereClause;
            }

            return (List<T>) jdbcTemplate.query(sql, new CustomBeanPropertyRowMapper<>(entity.getClass()), values.toArray());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    public static <T> List<T> queryBySql(String sql,Class<T> clazz) {
        return jdbcTemplate.query(sql, new CustomBeanPropertyRowMapper<>(clazz));
    }

    public static <T> Page<T> page(Page<T> page,String mapperSql,Map<String,Object> params) {
        try {


            String sql = MapperXmlParser.parseBySql(mapperSql, params);

            String countSql  = new SQL(){{
                SELECT("COUNT(1) as total");
                FROM("("+sql+") as ffe223f");
            }}.toString();

            Map<String, Object> count = jdbcTemplate.queryForMap(countSql);
            if(count.get("total")!=null){
                page.setTotal((Long) count.get("total"));
            }
            int pageNum = page.getPageNum();
            int pageSize = page.getPageSize();
            int offset = (pageNum < 1 ? 0 : (pageNum - 1) * pageSize);
            String qurey  =  String.format("SELECT * FROM ( %s ) as ffe223f LIMIT %d OFFSET %d ",
                    sql,pageSize,offset) ;


            List<T> result = jdbcTemplate.query(qurey, new PageBeanPropertyRowMapper<>(page.getClazz()));

            page.setList(result);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return page;
    }


    public static List<Map<String, Object>> queryBySql(String sql) {
        return jdbcTemplate.queryForList(sql);
    }

    public static Page<Map<String, Object>> queryPageBySql(Page<Map<String, Object>> page,String sql) {
        String countSql  = new SQL(){{
            SELECT("COUNT(1) as total");
            FROM("("+sql+") as ffe223f");
        }}.toString();
        Map<String, Object> count = jdbcTemplate.queryForMap(countSql);

        if(count.get("total")!=null){
            page.setTotal((Long) count.get("total"));
        }

        int pageNum = page.getPageNum();
        int pageSize = page.getPageSize();
        int offset = (pageNum < 1 ? 0 : (pageNum - 1) * pageSize);
        String qurey  =  String.format("SELECT * FROM ( %s ) as ffe223f LIMIT %d OFFSET %d ",
                sql,pageSize,offset) ;

        List<Map<String, Object>> maps = jdbcTemplate.queryForList(qurey);

        page.setList(maps);

        return page;
    }
}
